package com.cokemi.utils.tree.data;

import java.util.HashMap;

public class DefaultSqlDialect {

	public static String getChildNodeDataSql(HashMap<String, String> params)
			throws Exception {
		String sql = "";
		String parentId = params.get("parentId");
		int len = 3;
		if (params.get("nodeIdFieldLength") != null) {
			len = Integer.parseInt(params.get("nodeIdFieldLength"));
		}

		if (parentId == null && params.get("defaultRootId") != null) {
			parentId = params.get("defaultRootId");
		}

		String dbType = params.get("dbType");
		String tableName = params.get("tableName");
		String nodeIdField = params.get("nodeIdField");
		String nodeNameField = params.get("nodeNameField");
		String extendAttrField = params.get("extendAttrField");
		String nodeFilterCondition = params.get("nodeFilterCondition");
		if (dbType == null){
			dbType = "sybase";
		}
		if (extendAttrField == null) {
			extendAttrField = "''";
		}
		if (nodeFilterCondition == null) {
			nodeFilterCondition = "1=1";
		}

		if (dbType.equals("sybase")) {
			if (parentId == null || parentId.equals("")) {
				sql = "select A." + nodeIdField + " as nodeId, A."
						+ nodeNameField + " as nodeName, " + extendAttrField
						+ " as extendAttr, hasChildren = (select substring("
						+ nodeIdField + ",1," + len + ") from ";
				sql += tableName + " where "+nodeFilterCondition 
						+ " and (char_length(" + nodeIdField
						+ ") > " + len + ") and (substring(" + nodeIdField
						+ ",1," + len + ")=A." + nodeIdField + ") ";
				sql += "group by substring(" + nodeIdField + ",1," + len
						+ ")) ";
				sql += "from " + tableName + " A ";
				sql += "where char_length(" + nodeIdField + ") = " + len
						+ " and " + nodeFilterCondition + " order by "
						+ nodeIdField;
			} else {
				len = parentId.length() + len;
				sql = "select A." + nodeIdField + " as nodeId, A."
						+ nodeNameField + " as nodeName, " + extendAttrField
						+ " as extendAttr, hasChildren = (select substring("
						+ nodeIdField + ",1," + len + ") from ";
				sql += tableName + " where "+nodeFilterCondition 
						+ " and (char_length(" + nodeIdField
						+ ") > " + len + ") and (substring(" + nodeIdField
						+ ",1," + len + ")=A." + nodeIdField + ") ";
				sql += "group by substring(" + nodeIdField + ",1," + len
						+ ")) ";
				sql += "from " + tableName + " A ";
				sql += "where char_length(" + nodeIdField + ") = " + len
						+ " and " + nodeIdField + " like '" + parentId
						+ "%' and " + nodeFilterCondition + " order by "
						+ nodeIdField + "";
			}
		} else if (dbType.equals("oracle") || dbType.equals("mysql") || dbType.equals("db2")) {
			if (parentId == null) {
				sql = "select A." + nodeIdField + " as nodeId, A."
						+ nodeNameField + " as nodeName, " + extendAttrField
						+ " as extendAttr, (select substr(B." + nodeIdField
						+ ",1," + len + ") from ";
				sql += tableName + " B where "+nodeFilterCondition 
						+ " and (length(B." + nodeIdField + ") > "
						+ len + ") and (substr(B." + nodeIdField + ",1," + len
						+ ")=A." + nodeIdField + ") ";
				sql += "group by substr(B." + nodeIdField + ",1," + len
						+ ")) as hasChildren ";
				sql += "from " + tableName + " A ";
				sql += "where length(" + nodeIdField + ") = " + len + " and "
						+ nodeFilterCondition + " order by " + nodeIdField;
			} else {
				len = parentId.length() + len;
				sql = "select A." + nodeIdField + " as nodeId, A."
						+ nodeNameField + " as nodeName, " + extendAttrField
						+ " as extendAttr, (select substr(B." + nodeIdField
						+ ",1," + len + ") from ";
				sql += tableName + " B where "+nodeFilterCondition
						+ " and (length(B." + nodeIdField + ") > "
						+ len + ") and (substr(" + nodeIdField + ",1," + len
						+ ")=A." + nodeIdField + ") ";
				sql += "group by substr(B." + nodeIdField + ",1," + len
						+ ")) as hasChildren ";
				sql += "from " + tableName + " A ";
				sql += "where length(" + nodeIdField + ") = " + len + " and "
						+ nodeIdField + " like '" + parentId + "%'  and "
						+ nodeFilterCondition + " order by " + nodeIdField + "";
			}
		}
		return sql;
	}

	
	public static String getAllNodeDataSql(HashMap<String, String> params)
			throws Exception {
		String tableName = params.get("tableName");
		String nodeIdField = params.get("nodeIdField");
		String nodeNameField = params.get("nodeNameField");
		String extendAttrField = params.get("extendAttrField");
		if (extendAttrField == null) {
			extendAttrField = "''";
		}
		String sql = "select "+nodeIdField+" as nodeId," +
				nodeNameField+" as nodeName," +
				extendAttrField+" as extendAttr from "+ tableName + " order by "+nodeIdField;
		return sql;
	}
	
	
	public static String getMatchNodeDataSql(HashMap<String, String> params)
			throws Exception {
		String dbType = params.get("dbType");
		String tableName = params.get("tableName");
		String nodeIdField = params.get("nodeIdField");
		String searchValue = params.get("searchValue");
		String defaultRootId = params.get("defaultRootId");
		String nodeFilterCondition = params.get("nodeFilterCondition");

		if (dbType == null)
			dbType = "sybase";
		if (nodeFilterCondition == null)
			nodeFilterCondition = "1=1";
		String sql = "";
		if (dbType.equals("sybase") || dbType.equals("mysql")) {
			sql = "select "
					+ nodeIdField
					+ " from "
					+ tableName
					+ " where "
					+ new String(searchValue
							.getBytes(params.get("pageCharset")), params
							.get("dbCharset"));
			if (defaultRootId != null) {
				sql += " and " + nodeIdField + " like '" + defaultRootId
						+ "%' ";
			}
			if (nodeFilterCondition != null) {
				sql += " and " + nodeFilterCondition;
			}
			sql += " order by " + nodeIdField;
		}
		return sql;
	}

}
